Release 10.1A: OpenEdge Development:
Programming Interfaces

Accessing and incrementing sequences

Figure 1–8 lists Progress statements and functions you can use to access and increment sequence values from the 4GL.

Table 1–8: Sequence statements and functions 

Statement or function	Description
CURRENT–VALUE function	Returns the current value of a sequence.
DYNAMIC–CURRENT–VALUE function	Returns the current value of a dynamically-specified sequence.
NEXT–VALUE function	Increments and returns the incremented value for a sequence.
DYNAMIC–NEXT–VALUE function	Increments and returns the incremented value for a dynamically-specified sequence.
CURRENT–VALUE statement	Sets a new current value for a sequence.
DYNAMIC–CURRENT–VALUE statement	Sets a new current value for a dynamically-specified sequence.

Whenever a CURRENT–VALUE statement or NEXT–VALUE function changes the value of a sequence, the new value persists in the database where the sequence is defined until it is changed again, or the sequence is deleted from the database.

The sequence statements and functions have the following syntax:

Syntax

CURRENT-VALUE   (sequence [ , logical-dbname ] ) [ = expression ]

Syntax

NEXT-VALUE   (sequence [ , logical-dbname ] )

sequence

An identifier that specifies the name of a sequence defined in the Data Dictionary. Note that a sequence can have the same name as a database field, but they are distinct entities.

logical-dbname

An identifier that specifies the logical name of the database in which the sequence is defined. The database must be connected. You can omit this parameter only if you have a single database connected. Otherwise, you must specify the database in which the sequence is defined.

expression

An expression that evaluates to an integer value. The CURRENT–VALUE statement assigns the value of expression to the specified sequence. The value of expression must be within the range defined for the specified sequence.

The dynamic sequence statement and functions have the same general syntax, but allow you to specify the sequence and database dynamically at run time. Their syntax is as follows:

Syntax

DYNAMIC-CURRENT-VALUE   (sequence-expression, logical-dbname-expression ) [ = expression ]

Syntax

DYNAMIC-NEXT-VALUE   (sequence-expression, logical-dbname-expression )

sequence-expression

A character expression which evaluates to the name of a sequence defined in the Data Dictionary. Note that a sequence can have the same name as a database field, but they are distinct entities.

logical-dbname-expression

A character expression which evaluates to the logical name of the database in which the sequence is defined. The database must be connected. This argument is required and cannot be Unknown value (?).

expression

An expression that evaluates to an integer value. The DYNAMIC–CURRENT–VALUE statement assigns the value of expression to the specified sequence. The value of expression must be within the range defined for the specified sequence.

In general, use the CURRENT–VALUE and NEXT–VALUE functions for mission-critical applications that depend on access to reliable and orderly sequence values. Use the CURRENT–VALUE statement only for database maintenance and initialization, primarily during the development cycle. For more information on these functions and statements, see OpenEdge Development: Progress 4GL Reference .

Using the CURRENT–VALUE function

Use the CURRENT–VALUE function in any integer expression to retrieve the current value of a sequence without incrementing it. The current value of a sequence can be any one of the following:

The Initial value specified in the Dictionary.
The last value set with either the CURRENT–VALUE statement or the NEXT–VALUE function.

The following example gets the most recent customer number in the default database (maintained by the cust–num sequence), and displays each order record for that customer:

DEFINE VARIABLE current–cust AS INTEGER.               .               .               . current-cust = CURRENT-VALUE (cust-num). FOR EACH order WHERE cust-num = current-cust:     DISPLAY order.               .               .               .

Using the NEXT–VALUE function

Use the NEXT–VALUE function to increment a sequence by its defined positive or negative increment value. If the sequence cycles and NEXT–VALUE increments it beyond its Upper or Lower limit, the function sets and returns the defined Initial value for the sequence. If the sequence terminates and NEXT–VALUE tries to increment it beyond its Upper or Lower limit, the function returns the Unknown value (?) and leaves the sequence value unchanged.

The following example creates a new customer record with the next available customer number generated by the cust-num sequence:

DEFINE VARIABLE input-cust-num AS INTEGER. input-cust-num = NEXT-VALUE (cust-num, sports). FIND sports.customer WHERE cust-num = input-cust-num NO-ERROR. DO WHILE (AVAILABLE customer):   input-cust-num = NEXT-VALUE (cust-num, sports).   FIND sports.customer WHERE cust-num = input-cust-num     NO-ERROR. END. CREATE sports.customer. customer.cust-num = input-cust-num. UPDATE sports.customer EXCEPT cust-num.                   .                   .                   .

Because this example does not check the cust-num sequence for termination, it implies that cust-num is a cycling sequence. Because it does check for and ignore existing records containing the generated cust-num value, the example can reuse previously deleted (or otherwise skipped) customer numbers after the sequence cycles.

Using the CURRENT–VALUE statement

Use the CURRENT–VALUE statement to explicitly set a sequence to a new value. You can assign the defined initial value of a sequence, its upper or lower limit, or any integer value in between. Trying to set a value outside these bounds causes an error. Note that you cannot assign the Unknown value (?) to a sequence. Unlike the NEXT–VALUE function, the CURRENT–VALUE statement always sets a new sequence value in a transaction, starting one, if necessary.

Caution: Avoid using this statement in mission-critical applications. Use of this statement, especially in a multi-user context, can compromise the referential integrity of your database. Any database application designed to rely on the orderly values provided by sequences cannot reliably reset existing database fields according to potentially unexpected sequence values.

The purpose of the CURRENT–VALUE statement is to maintain a database off-line, under program control. Note that the Data Dictionary uses this statement when you create a new sequence to set the initial value. Possible uses include:

Restarting a terminating sequence that has terminated.
Otherwise, resetting a sequence value for a specific test condition.

Keep in mind that all such uses must respect and might require changes to the _Sequence table. This table contains one record for each sequence defined in the database. Creating a record in this table automatically allocates and assigns the data storage for a new sequence. Deleting a record in this table automatically releases the data storage for the specified sequence.

Progress provides fault detection that prevents a sequence from being created with inconsistent attribute values in the _Sequence table (such as, the minimum value greater than the maximum value). It also prevents the assignment of a new current value that is inconsistent with the corresponding attribute values in the _Sequence table (such as, a current value outside the minimum and maximum value boundary).

The following code fragment sets a new current value for sequence used for testing:

DEFINE VARIABLE SeqValue AS INTEGER. DO TRANSACTION ON ERROR UNDO, RETRY:     SET SeqValue LABEL "TEST-Stepper Start" WITH SIDE-LABELS.     CURRENT-VALUE(TEST-Stepper) = SeqValue. END. /* TRANSACTION */                   .                   .                   .

Progress also uses this statement in the Data Administration tool to load sequence definitions and values from dump-formatted text files. You can accomplish the same task implemented in the code fragment above using this tool. For more information, see OpenEdge Data Management: Database Administration .

Compiling procedures that reference sequences

When compiling procedures that use sequence statements and functions, do not run OpenEdge with the Time Stamp (-tstamp) startup parameter. All references to CURRENT-VALUE and NEXT–VALUE statements and functions in the r-code depend on CRC calculations made with the _Sequence metaschema table, and are not available with time stamping. CRC validation is now the default data consistency option for OpenEdge.

Resetting a terminating sequence

When a terminating sequence stops at its upper or lower limit, you can reset it by assigning a valid sequence value to it with the CURRENT–VALUE statement. You can also reset a stopped terminating sequence by changing it to a cycling sequence. The first use of the NEXT–VALUE function for the new cycling sequence resets the sequence to its initial value.

Referencing sequences within a WHERE clause

You cannot invoke sequence functions from within a WHERE clause. This generates a compiler error, because sequence expressions do not participate in the index resolution and optimization phase of the Compiler.

If you want to use a sequence value within a WHERE clause, set a variable or field to the sequence value, and reference the variable or field in your WHERE clause. See the sections that describe each sequence function for examples.